﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;

namespace SqlLiteTest
{
    class SqliteDatabase : ITestDatabase
    {

        System.Data.SQLite.SQLiteConnection _cnn = null;

        #region ITestDatabase Members

        void ITestDatabase.OpenAndClear()
        {
            var fi = new System.IO.FileInfo("sqlLiteTest.db3");
            if (fi.Exists) fi.Delete();

            _cnn = new System.Data.SQLite.SQLiteConnection("Data Source=sqlLiteTest.db3;Pooling=true;FailIfMissing=false");
            _cnn.Open();
        }

        void ITestDatabase.CreateIndividualTable()
        {
            using (DbCommand cmd = _cnn.CreateCommand())
            {
                cmd.CommandText = "CREATE TABLE INDIVIDUAL (ID int primary key, FIRSTNAME VARCHAR(20), MIDDLENAME VARCHAR(20), LASTNAME VARCHAR(20), DOB DATE, STATE VARCHAR(20))";
                cmd.ExecuteNonQuery();
            }
            // throw new NotImplementedException();
        }

        void ITestDatabase.InsertNewIndividual(int id, string firstname, string middlename, string lastname, DateTime dob, string state)
        {
            using (DbCommand cmd = _cnn.CreateCommand())
            {
                cmd.CommandText = string.Format("INSERT INTO INDIVIDUAL (ID, FIRSTNAME, MIDDLENAME, LASTNAME, DOB, STATE) VALUES({0}, '{1}', '{2}', '{3}', '{4}', '{5}');", id, firstname, middlename, lastname, dob.ToString("yyyy-MM-dd HH:mm:ss"), state);
                cmd.ExecuteNonQuery();
            }
        }

        IIndividual ITestDatabase.GetIndividualById(int id)
        {
            using (DbCommand cmd = _cnn.CreateCommand())
            {
                cmd.CommandText = string.Format("SELECT * FROM INDIVIDUAL WHERE ID=" + id);
                var reader = cmd.ExecuteReader();

                try
                {
                    if (reader.Read())
                    {
                        return GetNewIndividual(reader);
                    }
                    else return null;
                }
                finally
                {
                    reader.Close();
                }
            }
        }

        private static IIndividual GetNewIndividual(DbDataReader reader)
        {
            var res = new SqliteIndividual();
            res.ID = reader.GetInt32(0);
            res.FirstName = reader.GetString(1); //reader.GetString(1);
            res.MiddleName = reader.GetString(2);
            res.LastName = reader.GetString(3);
            res.Dob = reader.GetDateTime(4);
            res.State = reader.GetString(5);
            return res;
        }

        void ITestDatabase.SaveIndividual(IIndividual individual)
        {
            using (DbCommand cmd = _cnn.CreateCommand())
            {
                cmd.CommandText = string.Format("UPDATE INDIVIDUAL SET DOB='{1}' WHERE ID={0};", individual.ID, individual.Dob.ToString("yyyy-MM-dd HH:mm:ss"));
                cmd.ExecuteNonQuery();
            }
        }

        void ITestDatabase.Close()
        {
            // throw new NotImplementedException();
        }


        void ITestDatabase.BeginUpdates()
        {
            //throw new NotImplementedException();
        }

        void ITestDatabase.EndUpdates()
        {
            //throw new NotImplementedException();
        }

        #endregion

        #region ITestDatabase Members


        void ITestDatabase.CreateDobIndex()
        {
            using (DbCommand cmd = _cnn.CreateCommand())
            {
                cmd.CommandText = string.Format("CREATE INDEX DOB_IDX ON INDIVIDUAL (DOB)");
                cmd.ExecuteNonQuery();
            }
        }

        IEnumerable<IIndividual> ITestDatabase.IndividualsByAge()
        {
            using (DbCommand cmd = _cnn.CreateCommand())
            {
                cmd.CommandText = string.Format("SELECT * FROM INDIVIDUAL ORDER BY DOB");
                var reader = cmd.ExecuteReader();

                try
                {
                    while (reader.Read())
                    {
                        yield return GetNewIndividual(reader);
                    }
                }
                finally
                {
                    reader.Close();
                }

            }
        }

        #endregion
    }

    class SqliteIndividual : IIndividual
    {
        public int ID { get; set; }
        public string FirstName { get; set; }
        public string MiddleName { get; set; }
        public string LastName { get; set; }
        public DateTime Dob { get; set; }
        public string State { get; set; }
    }
}
